﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DelonixRegiaHMSys.Core.Interfaces;
using DelonixRegiaHMSys.Core.Models;

namespace DelonixRegiaHMSys.Infrastructure.Data
{
    public class EfReportQuery : IReportQuery
    {
        public IEnumerable<RoomStatusReport> GetAllRSR()
        {
            var context = new hmsDBEntities();
            return context.roomDetails.Select(rsr => new RoomStatusReport()
            {
                RoomNum = rsr.roomNum,
                RoomStatus = rsr.roomType,
            });
        }

        public IEnumerable<RoomOccupancyReport> GetAllROR()
        {
            var context = new hmsDBEntities();
            return context.roomDetails.Select(rsr => new RoomOccupancyReport(){
                RoomNum = rsr.roomNum
            });
        }

        public void InsertRO(RoomOccupancyReport ro)
        {
            var context = new hmsDBEntities();
            var newRecord = context.roomOccupancies.Create();
            newRecord.date = ro.Date;
            newRecord.roomNum = ro.RoomNum;
            newRecord.guestID = ro.GuestID;
            context.roomOccupancies.Add(newRecord);
            context.SaveChanges();
        }

        public IEnumerable<RoomGuestReport> GetAllRGR(int roomNum)
        {
            var context = new hmsDBEntities();
            var roomNumToRetrieve = context.roomDetails.FirstOrDefault(gr => gr.roomNum == roomNum);
            var query = from gp in context.guestDetails
                        where gp.guestID == roomNumToRetrieve.guestID
                        select new RoomGuestReport()
                        {
                            RoomNum = roomNumToRetrieve.roomNum,
                            GuestID = gp.guestID,
                            GuestFirstName = gp.guestFirstName,
                            GuestLastName = gp.guestLastName,
                            HomeAddress = gp.homeAddress,
                            Nationality = gp.nationality,
                            ContactNum = gp.contactNum,
                            EmailAddress = gp.emailAddress,
                            MailAddress = gp.mailAddress,
                        };
            return query;
        }

        public IEnumerable<GuestReport> GetAllGR()
        {
            var context = new hmsDBEntities();

            var query = from gp in context.guestDetails
                        join room in context.roomDetails on gp.guestID equals room.guestID
                        select new GuestReport()
                        {
                            RoomNum = room.roomNum,
                            GuestID = gp.guestID,
                            GuestFirstName = gp.guestFirstName,
                            GuestLastName = gp.guestLastName,
                            HomeAddress = gp.homeAddress,
                            Nationality = gp.nationality,
                            ContactNum = gp.contactNum,
                            EmailAddress = gp.emailAddress,
                            MailAddress = gp.mailAddress,
                        };
            return query;
        }

        public IEnumerable<Invoice> GetInvoicebyID(string invoiceID)
        {
            var context = new hmsDBEntities();

            var invoiceToRetrieve = context.invoices.FirstOrDefault(i => i.invoiceID == invoiceID);

            var query = from gp in context.guestDetails join ci in context.checkIns on invoiceToRetrieve.checkinID equals ci.checkinID join ro in context.roomDetails on invoiceToRetrieve.roomNum equals ro.roomNum
                        where gp.guestID == invoiceToRetrieve.guestID && ci.checkinID == invoiceToRetrieve.checkinID && ro.roomNum == invoiceToRetrieve.roomNum
                        select new Invoice()
                        {
                            InvoiceID = invoiceToRetrieve.invoiceID,
                            GuestID = gp.guestID,
                            GuestFN = gp.guestFirstName,
                            GuestLN = gp.guestLastName,
                            UserID = invoiceToRetrieve.userID,
                            CheckinID = invoiceToRetrieve.checkinID,
                            CheckInDateTime = ci.checkinDateTime,
                            CheckOutDateTime = invoiceToRetrieve.checkoutDateTime,
                            RoomNum = invoiceToRetrieve.roomNum,
                            RoomRates = ro.roomRates,
                            NumberOfNightsStayed = invoiceToRetrieve.numOfNightsStayed,
                            AddCosts = invoiceToRetrieve.addCosts,
                            TotalCosts = invoiceToRetrieve.totalCosts,
                            PaymentMode = ci.paymentMode
                        };

            return query;

        }

        public IEnumerable<Item> GetAllItem(string invoiceID)
        {
            var context = new hmsDBEntities();

            var query = from i in context.items
                        where i.invoiceID == invoiceID
                        select new Item()
                        {
                            ItemName = i.itemName,
                            Description = i.Description,
                            Cost = i.Cost
                        };

            return query;

        }

    }
}
